IF OBJECT_ID('dbo.csp_rptInstantAccount_Summary') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.csp_rptInstantAccount_Summary
END
GO

CREATE PROCEDURE dbo.csp_rptInstantAccount_Summary
(
	@Institution varchar(50)
,	@FromDate smalldatetime
,	@ThroughDate smalldatetime
)
AS

SELECT
	Title
,	TitleOrder
,	Institution
,	PostAwardManager
,	DaysAttachToActNotice2OrLess = SUM(CASE WHEN DaysAttachToActNotice2OrLess = 'YES' THEN 1 ELSE 0 END)
,	DaysAttachToActNotice3To15 = SUM(CASE WHEN DaysAttachToActNotice3To15 = 'YES' THEN 1 ELSE 0 END)
,	DaysAttachToActNoticeOver15 = SUM(CASE WHEN DaysAttachToActNoticeOver15 = 'YES' THEN 1 ELSE 0 END)
,	DaysAttachToFEAA15OrLess = SUM(CASE WHEN DaysAttachToFEAA15OrLess = 'YES' THEN 1 ELSE 0 END)
,	DaysAttachToFEAAOver15 = SUM(CASE WHEN DaysAttachToFEAAOver15 = 'YES' THEN 1 ELSE 0 END)
,	Pending = SUM(CASE WHEN DaysAttachmenttoFEAA IS NULL THEN 1 ELSE 0 END)
FROM
(
-- report A
select distinct Title='Instant Account Total',TitleOrder=1,* 
from tmpInstantAccountSetup 
where (UploadDate > '02/07/2008' or UploadDate is null) and Sponsor like 'NIH%' and InstrumentType not in ('Subcontract','Contract') 
UNION ALL
--Report B
select distinct Title='Backlog (Prior to 2/7/08)',TitleOrder=2,* 
from tmpInstantAccountSetup 
where UploadDate < '02/07/2008' and Sponsor like 'NIH%' and InstrumentType not in ('Subcontract','Contract') 
UNION ALL
--Report C
select distinct Title='Subcontract Total',TitleOrder=3,* 
from tmpInstantAccountSetup 
where (Sponsor not like 'NIH%' or InstrumentType in ('Subcontract','Contract'))
) AS x
WHERE
	(Institution IN (SELECT Item FROM dbo.Split (@Institution, ',')))
--	(Institution = @Institution OR @Institution IS NULL)
AND	(UploadDate >= @FromDate OR @FromDate IS NULL)
AND	(UploadDate < DATEADD(day,1,@ThroughDate) OR @ThroughDate IS NULL)
GROUP BY
	Title
,	TitleOrder
,	Institution
,	PostAwardManager
GO
